<?php
	require_once 'include/class.mysql.php';
	require_once 'include/constants.php';
	$db = new mysql();
	
	
	$sql_listing=$db->my_query("
SELECT 
	case 
		when account like '%espa%' then left(account,length(account)-3)
		else left(account,length(account)-2) 
	end as local,sum(@min:=
	CASE 
		when MINUTE(ChargedTime)=0 then HOUR(ChargedTime)
		else (HOUR(ChargedTime)+1) 
	END) as 'min',sum(
	CASE 
		WHEN MINUTE(ChargedTime) <=10 && HOUR(ChargedTime)=0 THEN 0
		WHEN `To`  LIKE '51%' THEN 
			CASE
				WHEN HOUR(`ChargedTime`)%10=0 then 0.5 + TRUNCATE(HOUR(`ChargedTime`),-1)/10
				ELSE 1 + TRUNCATE(HOUR(`ChargedTime`),-1)/10
			END
		WHEN `To` LIKE '346%'||'393%'||'521%'||'4915%'||'4916%'||'4917%'||'4919%' THEN @min*0.50
		ELSE 
			CASE	
				WHEN EXTRACT( HOUR FROM  `DateTime` ) >=20 && 
					EXTRACT( HOUR FROM  `DateTime` ) <=8 &&
					 `To`  LIKE '52%'||'34%'||'1%'||'86%'||'39%'||'49%'	 
				 THEN @min*0.30
				ELSE @min*0.50
			END
	END) as 'sale',sum(AmountPen) as 'cost',sum(
	CASE 
		WHEN MINUTE(ChargedTime) <=10 && HOUR(ChargedTime)=0 THEN 0
		WHEN `To`  LIKE '51%' THEN 
			CASE
				WHEN HOUR(`ChargedTime`)%10=0 then 0.5 + TRUNCATE(HOUR(`ChargedTime`),-1)/10 -AmountPen
				ELSE 1 + TRUNCATE(HOUR(`ChargedTime`),-1)/10 -AmountPen
			END
		WHEN `To` LIKE '346%'||'393%'||'521%'||'4915%'||'4916%'||'4917%'||'4919%' THEN @min*0.50 -AmountPen
		ELSE 
			CASE	
				WHEN EXTRACT( HOUR FROM  `DateTime` ) >=20 && 
					EXTRACT( HOUR FROM  `DateTime` ) <=8 &&
					 `To`  LIKE '52%'||'34%'||'1%'||'86%'||'39%'||'49%'	 
				 THEN @min*0.30 -AmountPen
				ELSE @min*0.50 -AmountPen
			END
	END) as 'profit'
FROM `nov`
group by local 
order by sale desc
limit 0,30
");
	echo "<body>";
	echo "<table>";
	while($array = $db->fetch_array($sql_listing)) {
		$color=($color='#eee')?'#ddd':'#eee';
		echo '<tr class="'.$color.'" >';
		echo "<td class='center'>".$array['local']."</td>";
		echo "<td>".$array['min']."</td>
		<td>".$array['sale']."</td>
		<td>".$array['cost']."</td>
		<td>".$array['profit']."</td>
		</tr>";
		$local=$array['local'].'|'.$local;
		$sale.=round($array['sale']);
		$cost.=round($array['cost']).',';
		$profit.=round($array['profit']).',';
	}
	echo "</table>";

	/*while($array = $db->fetch_array($sql_listing)) {
		$local.=$array['local'];
		$sale.=$array['sale'];
		$cost.=$array['cost'];
		$profit.=$array['profit'];
	}*/

	/*echo $local.'<br>';
	echo $sale.'<br>';*/
	$cost =rtrim($cost,',');
	$profit=rtrim($profit,',');
	echo "</body>";
	
	$url="http://chart.apis.google.com/chart?
chs=700x300&cht=bhs&
chco=4D89F9,C6D9FD&
chds=0,2200&chxt=y,x&
chxr=1,0,2200&
chbh=a,2,1&
chm=N,000000,-1,-1,11|N,000000,0,-1,11,,c|N,000000,1,-1,11,,c&chxl=0:|".$local."&chd=t:".$cost."|".$profit;
echo "<a href=\"$url\">$url</a>";
echo "<img src=\"$url\"/>";
?>